package com.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.junit.Test;

import com.bean.PlateBean;
import com.bean.PlateBean;
import com.util.DBSource;

public class PlateDao {
	DBSource db=new DBSource();
	Connection conn=null;
	PreparedStatement ps=null;
	public int addPlate(PlateBean bean){
		int rows=0;
		Date now=new Date();
		String year=now.getYear()+"";
		String month=now.getMonth()+1+"";
		String date=now.getDate()+"";
		String today=year+1900+"-"+month+"-"+date;
		try {
		conn=db.getConn();
		String insert="insert into plate (title,explains,userid,phonenum,prize,username,ctime) values(?,?,?,?,?,?,?)";
			ps=conn.prepareStatement(insert);
			ps.setString(1, bean.getTitle());
			ps.setString(2, bean.getExplains());
			ps.setInt(3, bean.getUserid());
			ps.setString(4, bean.getPhonenum());
			ps.setString(5, bean.getPrize());
			ps.setString(6, bean.getUsername());
			ps.setString(7, today);
			rows=ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			db.closeConn(conn);
		}
		return rows;
	}
//	@Test
//	public void test(){
//		Date now=new Date();
//		String year=now.getYear()+"";
//		String month=now.getMonth()+1+"";
//		String date=now.getDate()+"";
//		String today=year+1900+"-"+month+"-"+date;
//	}
	public int selOne(String username){
		int rows=0;
		try {
			conn=db.getConn();
			String select="select count(*) c from Plate where username=?";
			ps=conn.prepareStatement(select);
			ps.setString(1,username);
			ResultSet rs=ps.executeQuery();
			if(rs.next()){
				rows=rs.getInt("c");
			}
			} catch (SQLException e) {
				
				e.printStackTrace();
			}finally{
				db.closeConn(conn);
			}
			return rows;
	}
	public List<PlateBean> selectAll(){
		List<PlateBean> Plates=new ArrayList<PlateBean>();
		try {
		conn=db.getConn();
		String select="select * from Plate";
		ps=conn.prepareStatement(select);
		ResultSet rs=ps.executeQuery();
		while(rs.next()){
			PlateBean Plate=new PlateBean();
			Plate.setId(rs.getInt("id"));
			Plate.setTitle(rs.getString("Title"));
			Plate.setExplains(rs.getString("Explains"));
			Plate.setUserid(rs.getInt("userid"));
			Plate.setPhonenum(rs.getString("Phonenum"));
			Plate.setPrize(rs.getString("prize"));
			Plate.setUsername(rs.getString("username"));
			Plates.add(Plate);
		}
		} catch (SQLException e) {
			
			e.printStackTrace();
		}finally{
			db.closeConn(conn);
		}
		return Plates;
	}
}
